CREATE DATABASE BESTROUTE
GO

USE BESTROUTE
GO

CREATE TABLE TBL_POST (
  ID_POST INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  NAME_POST VARCHAR(100) NOT NULL
)
ENGINE=InnoDB;

CREATE TABLE TBL_STATUS_DELIVERY (
  ID_STATUS_DELIVERY INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  NAME_STATUS_DELIVERY VARCHAR(50) NOT NULL
)
ENGINE=InnoDB;

CREATE TABLE TBL_MOTOBOY (
  ID_MOTOBOY INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  NAME_MOTOBOY VARCHAR(100) NULL,
  RG_MOTOBOY VARCHAR(8)  NOT NULL,
  CPF_MOTOBOY VARCHAR(11) NULL,
  PHONE_MOTOBOY INTEGER UNSIGNED NULL,
  ADDRESS_MOTOBOY VARCHAR(100) NULL,
  ACTIVE_MOTOBOY INT NULL
)
ENGINE=InnoDB;


CREATE TABLE TBL_FUNC (
  ID_FUNC INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  NAME_FUNC VARCHAR(100) NOT NULL,
  RG_FUNC VARCHAR(11)  NOT NULL,
  CPF_FUNC VARCHAR(11)  NOT NULL,
  ID_POST_FUNC INTEGER UNSIGNED NOT NULL REFERENCES TBL_POST(ID_POST),
  PHONE_FUNC INT UNSIGNED NOT NULL,
  ADDRESS_FUNC VARCHAR(100) NOT NULL,
  ACTIVE_FUNC INT NOT NULL
)

ENGINE=InnoDB;

CREATE TABLE TBL_USER_MOTOBOY (
  ID_USER_MOTOBOY INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  ID_MOTOBOY INTEGER UNSIGNED NOT NULL REFERENCES TBL_MOTOBOY(ID_MOTOBOY),
  EMAIL_USER_MOTOBOY VARCHAR(100) NOT NULL,
  PASSWORD_MOTOBOY CHAR(32) NOT NULL,
  ACTIVE_USER_MOTOBOY INT NOT NULL  
)

ENGINE=InnoDB;

CREATE TABLE TBL_USER (
  ID_USER INTEGER UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  ID_FUNC_USER INTEGER UNSIGNED NOT NULL REFERENCES TBL_FUNC(ID_FUNC),
  EMAIL_FUNC_USER VARCHAR(100) NOT NULL,
  PASSWORD_USER CHAR(32) NOT NULL,
  ACTIVE_USER INT NOT NULL
)
ENGINE=InnoDB;

CREATE TABLE TBL_DELIVERY (
  ID_DELIVERY INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  ID_USER_DELIVERY INTEGER UNSIGNED NOT NULL REFERENCES TBL_USER(ID_USER),
  ID_MOTOBOY_DELIVERY INTEGER UNSIGNED NOT NULL REFERENCES ID_MOTOBOY(ID_MOTOBOY),
  DATE_START_DELIVERY DATETIME NOT NULL DEFAULT GETDATE(),
  DATE_END_DELIVERY DATETIME NULL,
  ID_STATUS_DELIVERY INTEGER UNSIGNED NOT NULL REFERENCES TBL_STATUS_DELIVERY(ID_STATUS_DELIVERY),
  LOCAL_DELIVERY INTEGER UNSIGNED NOT NULL,
  OBS_DELIVERY TEXT
)
ENGINE=InnoDB;
